<?php
/*
 * inc-exams.php
 * Retral/CSCD494 Winter 2009
 * Eastern Washington University
 *
 * File Description:
 * List past and current exams
 * 
 * Other:
 * Future TODO: Allow to span multiple pages only showing 10 (or a selectable amount) per page.
 */

/* 
 * Set up $page_name
 * This name will be compared to PAGE_NAME in NAV_URLS table (stored in session) and verify permission for given user.
 */
 
$page_name = "Exams";
$page_name = strtoupper($page_name);

$pm = $_SESSION['pagePermissions'];

// Is user logged in, and has permissions?
if (isset($_SESSION['loggedIn']) && $_SESSION['loggedIn'] !="" && $_SESSION['pagePermissions'][$page_name] == 1)
{
?>
	<span class="header-text">EXAMS</span><br>

	<h2>Exam List</h2>
	<?php
		$conn = oci_connect(ORA_CON_UN, ORA_CON_PW, ORA_CON_DB);
	?>


	<table border=0 width=100%>
		<tr>
			<td><u>Exam Date</u></td><td width=150><u>Date Created</u></td>
			<td width=150><u>Author</u></td><td width=80><u>Students</u></td>
		</tr><?PHP
		$query = '
			 SELECT ape_user.first_name, ape_user.last_name, 
				  date_created, date_scheduled, exam_id, author_id
			 FROM exam INNER JOIN ape_user 
			 ON author_id=ape_user.user_id
		    ORDER BY date_scheduled DESC';

		$stmtList = oci_parse($conn, $query);
		if(!oci_execute($stmtList)) {
			print "<P>Could not find exam list</P>";
		}

		$query = '
			SELECT count(DISTINCT student_id) AS "STUDENTS" FROM student_question
			WHERE exam_id=:examID';
		
		$stmt = oci_parse($conn, $query);
		
		while($exam = oci_fetch_assoc($stmtList)) {
			oci_bind_by_name($stmt, ':examID', $exam['EXAM_ID']);
			if(!oci_execute($stmt)) {
				print "<P>Faild to count students</P>";
			}

			$result = oci_fetch_assoc($stmt);
			
			print "<tr class='expand'>\n";
			print "\t\t<td><a ";
			print "href='?page=preview&exam=".$exam['EXAM_ID']."'>";
			print "".dateFormat($exam['DATE_SCHEDULED'])."</a></td>\n";
			print "\t\t<td>".dateFormat($exam['DATE_CREATED'])."</td>\n";
			print "\t\t<td><a href='?page=author&id=".$exam['AUTHOR_ID']."'>";
			print $exam['FIRST_NAME']." ".$exam['LAST_NAME']."</a></td>\n";
			print "\t\t<td>".$result['STUDENTS']."</td>\n";
			print "\t</tr>";
		}
		print "</table>";
		oci_free_statement($stmt);
		oci_free_statement($stmtList);
		oci_close($conn);
	?>

<?php
} //end if logged in
else
{
	echo "Invalid credentials";
	echo "<META http-equiv=\"refresh\" content=\"0;URL=index.php?page=login&ld=0\">";
}	
?>
